package com.biz.hunter.dao;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.biz.hunter.db.entity.Employee;
import com.biz.hunter.db.entity.EmployeePerson;
import com.biz.hunter.db.entity.Employer;
import com.biz.hunter.db.entity.Hunter;
import com.biz.hunter.db.entity.JobDesc;
import com.biz.hunter.db.entity.Person;
import com.biz.hunter.dbutil.BaseDAO;

@Repository
public class EmployeeDAO {

  static Logger logger = LoggerFactory.getLogger(EmployeeDAO.class);

  private static final String SQL_SAVE_EMP = "insert into tb_employee(personID, expectJob,expectCompany1," + "expectCompany2,expectCompany3,intentTime,"
                                             + "registed,domainId,comment, openid,phone) values(?,?,?," + "?,?,?," + "?,?,?, ?,?)";

  private static final String SQL_DELETE_EMP = "delete from tb_employee where empid=?";

 
  private static final String SQL_GET_ID = "select t1.*, t2.* from tb_person as t1, tb_employee t2 where t1.id = t2.personID and t2.personid = ? ";

  private static final String SQL_GET_OPENID = "select t1.*,t2.* from tb_person as t1, tb_employee t2  where t1.id = t2.personID and t2.openid = ? ";
  private static final String SQL_GET_OPENID_STATUS = "select t1.*,t2.* from tb_person as t1, tb_employee t2  "
  		+ "where t1.id = t2.personID and t2.openid = ? and t1.active=? ";

  private static final String SQL_UPDATE_EMP_PERSON_ID = "update tb_employee " + "set expectJob=?, expectCompany1=?, expectCompany2=?," + "expectCompany3=?, intentTime=?,"
                                                         + " registed =?,domainId=?,comment=? " + "where personID =?";

  private static final String SQL_UPDATE_EMP_ID = "update tb_employee " 
  + "set expectJob=?, expectJobName=?, expectCompany1=?, expectCompanyName1 = ? ,expectCompany2=?,expectCompanyName2 = ? ," 
      + "expectCompany3=?, expectCompanyName3 = ? , intentTime=?,"
             + " registed =?,domainId=?,comment=? ,state=? " + " where personId =?";

  private static final String SQL_GET_ALL_EMP = "select t1.*,t2.* from tb_person as t1, tb_employee t2  where t1.id = t2.personID ";
  private static final String SQL_GET_ALL_EMP_COUNT = "select COUNT(*) from tb_person as t1, tb_employee t2  where t1.id = t2.personID ";
  private static final String SQL_GET_ALL_EMP_WITH_LIMIT = "select t1.*,t2.* from tb_person as t1, tb_employee t2  where t1.id = t2.personID ";

  private static final String SEARCH_EMP_NAME = "select t1.*,t2.* from tb_person as t1, tb_employee t2  where t1.id = t2.personID ";

  public int delete(String Id) {

    if (Id != null) {

      int result = BaseDAO.execute(SQL_DELETE_EMP, Id);
      return result;
    } else {
      return 0;
    }
  }

  public List<Employee> getALLEmps() {

    return BaseDAO.queryForList(Employee.class, SQL_GET_ALL_EMP);
  }

  public Long getCountOfAll(String name, Integer companyId, String phoneNum) {
    String sql = SQL_GET_ALL_EMP_COUNT;
    if (StringUtils.hasText(name)) {
      sql += " and name like '%" + name + "%'";
    }
    if (companyId != null) {
      sql += " and companyId =" + companyId + " ";
    }
    if (StringUtils.hasText(phoneNum)) {
      sql += " and phone ='" + phoneNum + "' ";
    }
    return (Long)BaseDAO.queryOneRowOneCol(sql);
  }

  public List<Employee> getAllWithLimit(String name, Integer companyId, String phoneNum, long start, long length) {
    String sql = SQL_GET_ALL_EMP_WITH_LIMIT;
    if (StringUtils.hasText(name)) {
      sql += " and name like '%" + name + "%'";
    }
    if (companyId != null) {
      sql += " and companyId =" + companyId + " ";
    }
    if (StringUtils.hasText(phoneNum)) {
      sql += " and phone ='" + phoneNum + "' ";
    }
    sql += " LIMIT ?, ?";
    return BaseDAO.queryForList(Employee.class, sql, start, length);
  }

  public int update(Employee emp) {
    if (emp == null) {
      return -1;
    }

    int result = BaseDAO.execute(SQL_UPDATE_EMP_ID, emp.getExpectJob(), emp.getExpectJobName(), emp.getExpectCompany1(), emp.getExpectCompanyName1(),
                                 emp.getExpectCompany2(), emp.getExpectCompanyName2(),emp.getExpectCompany3(), 
                                 emp.getExpectCompanyName3(), emp.getIntentTime(),
                                 emp.getCompleted(), emp.getDomainId(), 
                                 emp.getComment(), emp.getState(), emp.getId());
    return result;
  }

  public int save(Employee emp) {
    if (emp == null) {
      return -1;
    }

    Long result = BaseDAO.save(SQL_SAVE_EMP, emp.getId(), emp.getExpectJob(), emp.getExpectCompany1(), emp.getExpectCompany2(), emp.getExpectCompany3(), emp.getIntentTime(),
                               emp.getCompleted(), emp.getDomainId(), emp.getComment(), emp.getOpenid(),emp.getPhone());

    return result.intValue();
  }

  private static final String SQL_GET_PERSON_ID = "select t1.*, t2.* from tb_person as t1, tb_employee t2  where t1.id = t2.personID and t1.id = ? ";

  public Employee getByPersonId(int personId) {
    return BaseDAO.queryForObject(Employee.class, SQL_GET_PERSON_ID, personId);
  }

  public Employee getById(int employeeid) {
    return BaseDAO.queryForObject(Employee.class, SQL_GET_ID, employeeid);
  }

  public Employee getByOpenId(String openId) {
    return getByOpenId(openId, Employee.activate);
  }

  public Employee getByOpenId(String openId, int status) {
      System.out.println("getByOpenId:"+openId);
    return BaseDAO.queryForObject(Employee.class, SQL_GET_OPENID_STATUS, openId, status);
  }

  public List<Employee> searchEmployees(String name, String phone) {
    String sql = SEARCH_EMP_NAME;
    if (StringUtils.hasText(name)) {
      sql += " and name like '%" + name + "%'";
    }
    if (StringUtils.hasText(phone)) {
      sql = SEARCH_EMP_NAME + " and mobile = '" + phone + "'  ";
    }
    return BaseDAO.queryForList(Employee.class, sql);

  }

  //获取职位相关的候选人信息，
  //1 person 中job相同的，公司 TODO： 单纯的猎头的推荐人现在还不能查询出来。person表单独不能匹配domain and expect job
  private static final String SEARCH_EMP_JOB = "select * from ("
  		+ "select t1.* from tb_person as t1, " 
  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
   + " and t1.companyId <> ? and  t1.roleId in(?,?) " 
      + "and t2.domainId in (?,?,?) "
      + " union "
      + "select t1.* from tb_person as t1, " 
  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
   + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
      + " and t1.companyId  in (?,?,?) "
      + " union"
      + " select tp.* from tb_person as tp where tp.cityId=? and tp.roleId in (?,?)"
      + "  and tp.companyId  in (?,?,?)"
      + " ) emp limit 0,10";

  public List<EmployeePerson> searchEmployeesByJob(JobDesc job) {
    int domainId = job.getDomainID();
    String jobName = job.getName();
    long jobId = job.getRoleid();
    int cityId = job.getCityId();
   long expectJobId = job.getExpectJobId();
    String companyName = job.getCompanyName();
    int companyId = job.getCompanyId();
    int companyId1 = job.getExpectcompanyid();
    int companyId2 = job.getExpectcompanyid2();
    int companyId3 = job.getExpectcompanyid3();
    int expectDomainId1 = job.getMajorDomainId1();
    int expectDomainId2 = job.getMajorDomainId2();
    int expectDomainId3 = job.getMajorDomainId3();
    
    
    return BaseDAO.queryForList(EmployeePerson.class, SEARCH_EMP_JOB, cityId, companyId, jobId, expectJobId, 
                                expectDomainId1,expectDomainId2, expectDomainId3, cityId,  jobId, expectJobId, jobId,
                               companyId1, companyId2, companyId3,
                               //20170219 add
                               cityId,  jobId, expectJobId, 
                               companyId1, companyId2, companyId3);
  }
  private static final String SEARCH_DIFF_EMP_JOB = "select * from ("
	  		+ "select t1.* from tb_person as t1, " 
	  		+ "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
	  		+ " and t1.companyId <> ? "
	  		+ " and ( t1.roleId in(?,?) or t2.expectJob = ? )" 
	  		+ " and (t1.domainId in (?,?,?) ";
	  public List<EmployeePerson> searchDiffEmployeesByJob(JobDesc job,long jobid,int active) {
//		String sql=SEARCH_DIFF_EMP_JOB;
//	    long jobId = job.getRoleid();
//	    int cityId = job.getCityId();
//	   long expectJobId = job.getExpectJobId();
//	    int companyId = job.getCompanyId();
//	    int companyId1 = job.getExpectcompanyid();
//	    int companyId2 = job.getExpectcompanyid2();
//	    int companyId3 = job.getExpectcompanyid3();
//	    int expectDomainId1 = job.getMajorDomainId1();
//	    int expectDomainId2 = job.getMajorDomainId2();
//	    int expectDomainId3 = job.getMajorDomainId3();
//	    if(expectDomainId1!=0){
//	    	sql+=" or t2.majorDomainId2="+expectDomainId1+" or t2.majorDomainId3="+expectDomainId1+"";
//	    }
//	    if(expectDomainId2!=0){
//	    	sql+=" or t2.majorDomainId2="+expectDomainId2+" or t2.majorDomainId3="+expectDomainId2+"";
//	    }
//	    if(expectDomainId3!=0){
//	    	sql+=" or t2.majorDomainId2="+expectDomainId3+" or t2.majorDomainId3="+expectDomainId3+"";
//	    }
//	    sql+= ")"
//	    	+ " union "
//			+ "select t1.* from tb_person as t1, "
//			+ "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
//			+ " and ( t1.roleId in(?,?) or t2.expectJob = ? ) "
//			+ " and t1.companyId  in (?,?,?) "
//			+ " ) emp ";
//	    logger.info("jobdesc id is="+job.getId());
//	    logger.info("searchEmployeesByJob...sql="+sql);
//	    return BaseDAO.queryForList(EmployeePerson.class, sql, cityId, companyId, jobId, expectJobId, jobId,
//	                                expectDomainId1,expectDomainId2, expectDomainId3, cityId,  jobId, expectJobId, jobId,
//	                               companyId1, companyId2, companyId3);

          long jobIds = (long)job.getRoleid();
          Integer jobId=(int)jobIds;
          int cityId = job.getCityId();
          long expectJobIds = (long)job.getExpectJobId();
          Integer expectJobId=(int)expectJobIds;
          int companyId = job.getCompanyId();
          int companyId1 = job.getExpectcompanyid();
          int companyId2 = job.getExpectcompanyid2();
          int companyId3 = job.getExpectcompanyid3();
          int expectDomainId1 = job.getMajorDomainId1();
          int expectDomainId2 = job.getMajorDomainId2();
          int expectDomainId3 = job.getMajorDomainId3();
          StringBuilder sb=new StringBuilder();
          if(expectDomainId1>0) {
              sb.append(expectDomainId1);
          }
          if(expectDomainId2>0) {
              sb.append(","+expectDomainId2);
          }
          if(expectDomainId3>0) {
              sb.append(","+expectDomainId3);
          }
          String ids=""+sb;
          StringBuilder sbcompany=new StringBuilder();
          if(companyId1>0) {
              sbcompany.append(companyId1);
          }
          if(companyId2>0) {
              sbcompany.append(","+companyId2);
          }
          if(companyId3>0) {
              sbcompany.append(","+companyId3);
          }
          String companys=""+sbcompany;
          StringBuilder orderbycom=new StringBuilder();
          if(companyId3>0) {
              orderbycom.append(companyId3);
          }
          if(companyId2>0&companyId3>0) {
              orderbycom.append(","+companyId2);
          }else if(companyId2>0&companyId3<=0){
              orderbycom.append(companyId2);
          }
          if(companyId1>0&companyId2>0) {
              orderbycom.append(","+companyId1);
          }else if(companyId1>0&companyId2<=0){
              orderbycom.append(companyId1);
          }
          String orderbycoms=""+orderbycom;
          StringBuilder orderbyid=new StringBuilder();
          if(expectDomainId3>0) {
              orderbyid.append(expectDomainId3);
          }
          if(expectDomainId2>0&expectDomainId3>0) {
              orderbyid.append(","+expectDomainId2);
          }else if(expectDomainId2>0&expectDomainId3==0){
              orderbyid.append(expectDomainId2);
          }
          if(expectDomainId1>0&expectDomainId2>0) {
              orderbyid.append(","+expectDomainId1);
          }else if(expectDomainId1>0&expectDomainId2==0){
              orderbyid.append(expectDomainId1);
          }
          String orderbyids=""+orderbyid;
          String sql="select * from ("
                  + "select t1.*,t2.expectJob as expectJob from tb_person as t1, "
                  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = "+cityId+" "
                  + " and t1.companyId <> "+companyId+" and  ( t1.roleId in("+jobId+","+expectJobId+") or t2.expectJob = "+jobId+" ) "
                  + " and t1.active="+active+" and t1.domainId in ("+ids+") ";
          sql+=" union"
                  + " select t1.*,t2.expectJob as expectJob from tb_person as t1,"
                  + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = "+cityId+""
                  + " and ( t1.roleId in("+jobId+","+expectJobId+") or t2.expectJob = "+jobId+" )"
                  + " and t1.active="+active+" and t1.companyId  in ("+companys+")"
                  + " union"
                  + " select t1.*,t2.expectJob as expectJob from tb_person as t1,"
                  + "tb_employee t2  where t1.id = t2.personID and t1.companyId<>0  and t1.cityId = "+cityId+""
                  + " and ( t1.roleId in("+jobId+","+expectJobId+") or t2.expectJob = "+jobId+")"
                  + " and t1.active="+active+" and t2.majorDomainId2  in ("+ids+")"
                  + " union"
                  + " select t1.*,t2.expectJob as expectJob from tb_person as t1,"
                  + "tb_employee t2  where t1.id = t2.personID and t1.companyId<>0  and t1.cityId = "+cityId+""
                  + " and ( t1.roleId in("+jobId+","+expectJobId+") or t2.expectJob = "+jobId+" )"
                  + " and t1.active="+active+" and t2.majorDomainId3  in ("+ids+")"
                  + ") emp order by field(domainId,"+orderbyids+") DESC,field(companyId,"+orderbycoms+") DESC,field(roleId,"+jobId+") DESC,field(expectJob,"+expectJobId+") DESC limit 0,50";
          logger.info("searchDiffActiveEmployeesByJob sql="+sql);
          List<EmployeePerson> emps= BaseDAO.queryForList(EmployeePerson.class,sql);
          return emps;
	  }
	  

  protected static final String SQL_GET_BY_PHONE = "select * from tb_employee where phone = ? and openId = ?";

  public Employee getbyPhone(String phone, String openId) {
    return BaseDAO.queryForObject(Employee.class, SQL_GET_BY_PHONE, phone, openId);
  }

  protected static final String SQL_SAVE_SECURECODE = "insert into tb_employee(openid, phone, secureCode,expiredTime,"
      + "state,active ,personId) " + "values(?,?,?,?,?,?,?)";

  public int saveSecureCode(Employee emp) {
    if (emp == null) {
      return -1;
    }
    Long id = BaseDAO.save(SQL_SAVE_SECURECODE, emp.getOpenid(), emp.getPhone(), emp.getSecureCode(), emp.getExpiredTime(), 
                           emp.getState(), emp.getActive(),emp.getPersonId());
    if (id == null) {
      return -1;
    }

    return id.intValue();
  }

  protected static final String SQL_BINDED_PHONE = "update tb_employee set state=?, headImgUrl =?, active = ?"
  + " where phone =? and openid =?";

  public long bindedPhone(Employee emp) {
    int result = BaseDAO.execute(SQL_BINDED_PHONE, Person.State.binded.toString(), emp.getHeadImgUrl(),
                                 emp.getActive(), emp.getPhone(), emp.getOpenid());
    return result;
  }

  private static final String UPDATEPROFILE_INSERT = "insert into tb_employee(personID, expectJob,expectCompany1," + "expectCompany2,expectCompany3,intentTime,"
                                                     + "registed,domainId,comment, openid) values(?,?,?," + "?,?,?," + "?,?,?, ?)";
  private static final String UPDATE_EXCEPT = "update tb_employee " + "set expectJob=?, expectCompany1=?, expectCompany2=?," + "expectCompany3=?, intentTime=?,"
                                              + " registed =?,domainId=?,comment=? " + "where personID =?";

  private static final String UPDATE_PROFILE = "update tb_employee " 
  + "set name=?, workage=?, cityId=?," + "cityname=?, companyName=?, jobName=?, "
+ " domainName =?,domainId=?,comment=? " + "where personID =?";

  
  
  private static final String UPDATE_EMP_PROFILE = "update tb_employee " 
      + "set expectJob=?, expectJobName=?, expectCompany1=?, expectCompanyName1 = ? ,expectCompany2=?,expectCompanyName2 = ? ," 
          + "expectCompany3=?, expectCompanyName3 = ? , intentTime=?,"
                 + " registed =?,comment=? ,state=? ,jobName=?" + ",majorDomainId1 = ? ,"
      + "majorDomainId2=? ,"
      + "majorDomainId3=?  where personId =?";

  public int updateProfile(Employee emp) {
    if (emp == null) {
      return -1;
    }

    int result = BaseDAO.execute(UPDATE_EMP_PROFILE, emp.getExpectJob(), emp.getExpectJobName(), emp.getExpectCompany1(), emp.getExpectCompanyName1(),
                                 emp.getExpectCompany2(), emp.getExpectCompanyName2(),emp.getExpectCompany3(), 
                                 emp.getExpectCompanyName3(), emp.getIntentTime(),
                                 emp.getCompleted(),
                                 emp.getComment(), emp.getState(),emp.getRoleName(), 
                                 emp.getMajorDomainId1(),emp.getMajorDomainId2(),
                                 emp.getMajorDomainId3(),emp.getId());
      System.out.println("[updateProfile SQL]: "+UPDATE_EMP_PROFILE+" rows:"+result);
    return result;
  }
  String UPDATE_EMP_domain  = "update tb_employee " 
      + "set domainId=?, domainName =? , headimgurl = ?  where personId =?";
 
  public void updateDomain(Employee emp) {
    int result = BaseDAO.execute(UPDATE_EMP_domain,  emp.getDomainId(),emp.getDomainName(),
                                 emp.getHeadImgUrl(), 
                                 emp.getId());
      System.out.println("[updateDomain SQL]: "+UPDATE_EMP_domain+" rows:"+result);
    
  }

  public List<Employee> searchEmployeesByhr(Employer employer) {
    // TODO Auto-generated method stub
    return null;
  }
  
  private static final String SQL_EXIST_PHONE="SELECT t1.*,t2.* from tb_person t1 LEFT JOIN tb_employee t2 on t1.id=t2.personid where t1.phone=?  and t1.active=?";
  public EmployeePerson existPhone(String phone){
	  return BaseDAO.queryForObject(EmployeePerson.class, SQL_EXIST_PHONE, phone,Employee.activate);
  }

  public EmployeePerson getByEpId(int employeeid){
	  return BaseDAO.queryForObject(EmployeePerson.class, SQL_GET_ID, employeeid);
  }
  
  

  private static final String SQL_GET_EMP_ID = "select t1.*, t2.* from tb_person as t1, tb_employee t2 where t1.id = t2.personID and t2.empid= ? ";
  public Employee getByEmpId(int employeeid){
	 return BaseDAO.queryForObject(Employee.class, SQL_GET_EMP_ID, employeeid);
  }

//    public int updatePersonInP2(Employee emp) {
//
//    }
//
//    public int updateEmployeeInP2(Employee emp) {
//
//    }


    //更多候选人匹配信息
  //去除已在邀约中的候选人，取其差集
//  private static final String SEARCH_DIFF_EMP_JOB="select * from ("
//		+ "select t1.* from tb_person as t1, " 
//	    + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
//	    + " and t1.companyId <> ? and  t1.roleId in(?,?) " 
//	    + "and t2.domainId in (?,?,?) "
//	    + " union "
//	    + "select t1.* from tb_person as t1, " 
//	    + "tb_employee t2  where t1.id = t2.personID  and t1.cityId = ? "
//	    + " and  ( t1.roleId in(?,?) or t2.expectJob = ? ) " 
//	    + " and t1.companyId  in (?,?,?) "
//	    + " union"
//	    + " select tp.* from tb_person as tp where tp.cityId=? and tp.roleId in (?,?)"
//	    + "  and tp.companyId  in (?,?,?)"
//	    + " ) emp where id not in(select b.id as id from tb_recommend a ,tb_jobtitle d, tb_person b "
//	    + "left join tb_company c on b.companyid=c.id "
//	    + "where a.personid = b.id and d.id=b.roleid "
//	    + "and a.jobid=?)";
//  
//  public List<EmployeePerson> searchDiffEmployeesByJob(JobDesc job,long jobid){
//	    long jobId = job.getRoleid();
//	    int cityId = job.getCityId();
//	   long expectJobId = job.getExpectJobId();
//	    int companyId = job.getCompanyId();
//	    int companyId1 = job.getExpectcompanyid();
//	    int companyId2 = job.getExpectcompanyid2();
//	    int companyId3 = job.getExpectcompanyid3();
//	    int expectDomainId1 = job.getMajorDomainId1();
//	    int expectDomainId2 = job.getMajorDomainId2();
//	    int expectDomainId3 = job.getMajorDomainId3();
//	    return BaseDAO.queryForList(mployeePerson.class, SEARCH_DIFF_EMP_JOB, cityId, companyId, jobId, expectJobId,
//                expectDomainId1,expectDomainId2, expectDomainId3, cityId,  jobId, expectJobId, jobId,
//               companyId1, companyId2, companyId3,
//               //20170219 add
//               cityId,  jobId, expectJobId, 
//               companyId1, companyId2, companyId3,jobid);
//  }
//

    public static final String GET_UNIONID= "select * from tb_employee " +
            " where openid = ?";
    public Employee getEmpByUnionid(String unionid) {
        return BaseDAO.queryForObject(Employee.class, GET_UNIONID, unionid);
    }

}
